/* 
*@module: uploadExcel
*@author: 刘千红
*/
const db = require('../../config/db.js') //引入数据库
const Sequelize = db.theDb
const sample_lab_ground_InfoSchema = Sequelize.import('../../schema/sample_lab_ground_info.js');
const sample_lab_water_InfoSchema = Sequelize.import('../../schema/sample_lab_water_info.js');
const project_infoSchema = Sequelize.import('../../schema/project_info.js');
const point_infoSchema = Sequelize.import('../../schema/point_info.js');
const sample_detector_water_info = Sequelize.import('../../schema/sample_detector_water_info.js');
const sample_detector_ground_info = Sequelize.import('../../schema/sample_detector_ground_info.js');
const R_table_ground_info = Sequelize.import('../../schema/r2_ground.js');
const R_table_water_info = Sequelize.import('../../schema/r2_water.js');
const reference_info = Sequelize.import('../../schema/reference_info.js');



/**
 *插入数据库表格，并根据日期更新数据信息    
 *
 * @class ExcelDataUpload
 */
class ExcelDataUpload {
  static async upload(data, database_type) {

    var count
    var Update = []
    var insert = []
    var insertAndUpdate = []
    var datefalse

    if (database_type == 5) {


      for (let i of data) {
        var a = new Date(i.date);
        var y = a.getFullYear();
        var m = a.getMonth() + 1; //月份从0开始，所以要+1
        var d = a.getDate();
        var myday1 = y + "." + m + "." + d;//判断日期是否正确
        var date_now = new Date();
        //得到当前年份
        var year = date_now.getFullYear();
        //得到当前月份
        //注：
        //  1：js中获取Date中的month时，会比当前月份少一个月，所以这里需要先加一
        //  2: 判断当前月份是否小于10，如果小于，那么就在月份的前面加一个 '0' ， 如果大于，就显示当前月份
        var month = date_now.getMonth() + 1 < 10 ? "0" + (date_now.getMonth() + 1) : (date_now.getMonth() + 1);
        //得到当前日子（多少号）
        var date = date_now.getDate() < 10 ? "0" + date_now.getDate() : date_now.getDate();
        var myday2 = year + "." + month + "." + date;

        if (myday1 != i.date || myday2 < i.date) {

          datefalse++
        }

        if (datefalse == 0) {//日期正确才进行更新和插入
          await sample_lab_ground_InfoSchema.findCreateFind(
            {
              where: {
                sample_num: i.sample_num,
                point_num: i.point_num,
                project_num: i.project_num,
              },
              defaults: i,
            }).spread((user, created) => {
              if (created === false) { //不用新插入才能进行更新
                sample_lab_ground_InfoSchema.update(
                  i
                  , {
                    where: {
                      sample_num: i.sample_num,
                      assess_type: i.assess_type,
                      point_num: i.point_num,
                      project_num: i.project_num,
                      date: {
                        $lt: new Date(i.date),
                      }
                    }
                  }).then(item => {
                    if (item[0] == 1) {
                      Update.push(i)
                    }
                  })//记录更新的对象
              } else {
                insert.push(i)//记录插入的对象
              }
            })
        }
      }
    } else if (database_type == 3) {


      for (let i of data) {
        var a = new Date(i.date);
        var y = a.getFullYear();
        var m = a.getMonth() + 1; //月份从0开始，所以要+1
        var d = a.getDate();
        var myday1 = y + "." + m + "." + d;//判断日期是否正确

        var date_now = new Date();
        //得到当前年份
        var year = date_now.getFullYear();
        //得到当前月份
        //注：
        //  1：js中获取Date中的month时，会比当前月份少一个月，所以这里需要先加一
        //  2: 判断当前月份是否小于10，如果小于，那么就在月份的前面加一个 '0' ， 如果大于，就显示当前月份
        var month = date_now.getMonth() + 1 < 10 ? "0" + (date_now.getMonth() + 1) : (date_now.getMonth() + 1);
        //得到当前日子（多少号）
        var date = date_now.getDate() < 10 ? "0" + date_now.getDate() : date_now.getDate();
        var myday2 = year + "." + month + "." + date;

        if (myday1 != i.date || myday2 < i.date) {

          datefalse++
        }

        if (datefalse == 0) {//日期正确才进行更新和插入
          await sample_lab_water_InfoSchema.findOrCreate(
            {
              where: {
                sample_num: i.sample_num,
                point_num: i.point_num,
                project_num: i.project_num,
              },
              defaults: i,
            }).spread((user, created) => {//不用新插入才能进行更新
              if (created === false) {
                sample_lab_water_InfoSchema.update(
                  i
                  , {
                    where: {
                      sample_num: i.sample_num,
                      assess_type: i.assess_type,
                      point_num: i.point_num,
                      project_num: i.project_num,
                      date: {
                        $lt: new Date(i.date),
                      }
                    }
                  }).then(item => {

                    if (item[0] == 1) {
                      Update.push(i)//记录更新的对象
                    }
                  })

              } else {
                insert.push(i)//记录插入的对象
              }

            })
        }
      }
    } else if (database_type == 1) { //更新地块信息的数据库表格信息
      for (let i of data) {
        await project_infoSchema.findCreateFind( //首先判断数据库中是否含有此条数据没有就创建此条数据
          {
            where: {//约束条件
              project_name: i.project_name,
              project_num: i.project_num,
              project_area: i.project_area,
              project_type: i.project_type,
            },
            defaults: i,
          }).spread((user, created) => { // findCreateFind函数的扩展
            if (created === false) {
              project_infoSchema.update( //精过上面的步骤判断出数据库中含有该条数据就更新该条数据
                i
                , {
                  where: { //约束条件
                    project_name: i.project_name,
                    project_num: i.project_num,
                    project_area: i.project_area,
                    project_type: i.project_type,
                  }

                }).then(item => {
                  if (item[0] == 1) {
                    Update.push(i)//如果汉语对象则将该对象传入数组Update中
                  }
                })
            } else {
              insert.push(i)//如果created === true 则说明数据库中没有该条数据泽井该条数据放入数组insert中
            }
          })
      }
    } else if (database_type == 2) {//更新监测点位信息的数据库表格
      for (let i of data) {
        await point_infoSchema.findOrCreate(//首先判断数据库中是否含有此条数据没有就创建此条数据
          {
            where: {//约束条件
              assess_type: i.assess_type,
              point_num: i.point_num,
              project_num: i.project_num,
            },
            defaults: i,
          }).spread((user, created) => {
            if (created === false) {

              point_infoSchema.update( //精过上面的步骤判断出数据库中含有该条数据就更新该条数据 
                i
                , {
                  where: {
                    assess_type: i.assess_type,
                    point_num: i.point_num,
                    project_num: i.project_num,
                  }
                }).then(item => {
                  if (item[0] == 1) {
                    Update.push(i)
                  }
                })
            } else {
              insert.push(i)
            }

          })
      }
    } else if (database_type == 4) {
      for (let i of data) {
        var a = new Date(i.date);
        var y = a.getFullYear();
        var m = a.getMonth() + 1; //月份从0开始，所以要+1
        var d = a.getDate();
        var myday1 = y + "." + m + "." + d;
        var date_now = new Date();
        //得到当前年份
        var year = date_now.getFullYear();
        //得到当前月份
        //注：
        //  1：js中获取Date中的month时，会比当前月份少一个月，所以这里需要先加一
        //  2: 判断当前月份是否小于10，如果小于，那么就在月份的前面加一个 '0' ， 如果大于，就显示当前月份
        var month = date_now.getMonth() + 1 < 10 ? "0" + (date_now.getMonth() + 1) : (date_now.getMonth() + 1);
        //得到当前日子（多少号）
        var date = date_now.getDate() < 10 ? "0" + date_now.getDate() : date_now.getDate();
        var myday2 = year + "." + month + "." + date;

        if (myday1 != i.date || myday2 < i.date) {

          datefalse++
        }


        if (datefalse == 0) {
          await sample_detector_water_info.findOrCreate(
            {
              where: {
                sample_num: i.sample_num,
                point_num: i.point_num,
                project_num: i.project_num,
              },
              defaults: i,
            }).spread((user, created) => {
              if (created === false) {
                sample_detector_water_info.update(
                  i
                  , {
                    where: {
                      sample_num: i.sample_num,
                      assess_type: i.assess_type,
                      point_num: i.point_num,
                      project_num: i.project_num,
                      date: {
                        $lt: new Date(i.date),
                      }
                    }
                  }).then(item => {

                    if (item[0] == 1) {
                      Update.push(i)
                    }
                  })

              } else {
                insert.push(i)
              }

            })
        }
      }
    } else if (database_type == 6) {

      for (let i of data) {
        var a = new Date(i.date);
        var y = a.getFullYear();
        var m = a.getMonth() + 1; //月份从0开始，所以要+1
        var d = a.getDate();
        var myday1 = y + "." + m + "." + d;

        var date_now = new Date();
        //得到当前年份
        var year = date_now.getFullYear();
        //得到当前月份
        //注：
        //  1：js中获取Date中的month时，会比当前月份少一个月，所以这里需要先加一
        //  2: 判断当前月份是否小于10，如果小于，那么就在月份的前面加一个 '0' ， 如果大于，就显示当前月份
        var month = date_now.getMonth() + 1 < 10 ? "0" + (date_now.getMonth() + 1) : (date_now.getMonth() + 1);
        //得到当前日子（多少号）
        var date = date_now.getDate() < 10 ? "0" + date_now.getDate() : date_now.getDate();
        var myday2 = year + "." + month + "." + date;

        if (myday1 != i.date || myday2 < i.date) {

          datefalse++
        }

        if (datefalse == 0) {
          await sample_detector_ground_info.findCreateFind(
            {
              where: {
                sample_num: i.sample_num,
                point_num: i.point_num,
                project_num: i.project_num,
              },
              defaults: i,
            }).spread((user, created) => {
              if (created === false) {
                sample_detector_ground_info.update(
                  i
                  , {
                    where: {
                      sample_num: i.sample_num,
                      assess_type: i.assess_type,
                      point_num: i.point_num,
                      project_num: i.project_num,
                      date: {
                        $lt: new Date(i.date),
                      }
                    }
                  }).then(item => {
                    if (item[0] == 1) {
                      Update.push(i)
                    }
                  })
              } else {
                insert.push(i)
              }
            })
        }
      }
    }



    if (Update.length != 0 && insert.length != 0) {
      count = Update.length + insert.length
      insertAndUpdate = Update.concat(insert)
    } else if (insert.length == 0 && Update.length != 0) {
      count = Update.length

      insertAndUpdate = [].concat(Update)

    } else if (Update.length == 0 && insert.length != 0) {
      count = insert.length

      insertAndUpdate = [].concat(insert)

    } else if (Update.length == 0 && insert.length == 0) {
      count = 0

    }
    return [count, insertAndUpdate]
  }//上传表格信息进入数据库


  /**
   *更新土壤背景表的污染数量count
   *
   * @static
   * @param {*} item
   * @param {*} sample_num
   * @param {*} point_num
   * @param {*} assess_type
   * @param {*} project_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateCount(item, sample_num, point_num, assess_type, project_num) {
    await sample_lab_ground_InfoSchema.update(
      item
      , {
        where: {
          sample_num: sample_num,
          point_num: point_num,
          assess_type: assess_type,
          project_num: project_num,
        }
      })
    return true
  }//更新土壤背景表的count

  /**
   *更新水背景表的污染数量count
   *
   * @static
   * @param {*} item
   * @param {*} sample_num
   * @param {*} point_num
   * @param {*} assess_type
   * @param {*} project_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateWaterCount(item, sample_num, point_num, assess_type, project_num) {
    await sample_lab_water_InfoSchema.update(
      item
      , {
        where: {
          sample_num: sample_num,
          point_num: point_num,
          assess_type: assess_type,
          project_num: project_num
        }
      })
    return true
  }//更新水背景表的count

  /**
   *更新土壤实时监测表的污染数量count
   *
   * @static
   * @param {*} item
   * @param {*} sample_num
   * @param {*} point_num
   * @param {*} assess_type
   * @param {*} project_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateDetectorWaterCount(item, sample_num, point_num, assess_type, project_num) {
    await sample_detector_water_info.update(
      item
      , {
        where: {
          sample_num: sample_num,
          point_num: point_num,
          assess_type: assess_type,
          project_num: project_num
        }
      })
    return true
  }//更新土壤实时监测表的count


  /**
   *更新水实时监测表的污染数量count
   *
   * @static
   * @param {*} item
   * @param {*} sample_num
   * @param {*} point_num
   * @param {*} assess_type
   * @param {*} project_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateDetectorGroundCount(item, sample_num, point_num, assess_type, project_num) {
    await sample_detector_ground_info.update(
      item
      , {
        where: {
          sample_num: sample_num,
          point_num: point_num,
          assess_type: assess_type,
          project_num: project_num
        }
      })
    return true
  }//更新水实时监测表的count


  /**
   *查询监测点位表（point_info）中是否含有监测点位
   *
   * @static
   * @param {*} project_num
   * @param {*} point_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findPoint_num(project_num, point_num) {

    const have_point_num = await point_infoSchema.findAll({
      attributes: ['project_num', 'point_num'],
      where: {
        project_num,
        point_num,
      }

    })

    return have_point_num
  }

  /**
   *查询地块信息表（project_info）中是否含有地块信息
   *
   * @static
   * @param {*} project_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findProject_num(project_num) {

    const True = await project_infoSchema.findAll({
      attributes: ['project_num'],
      where: {
        project_num,
      }
    })

    return True
  }



  /**
   *查询土壤实时监测表中是否含有相应监测点的样本信息
   *
   * @static
   * @param {*} sample_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findDetectorGroundProject_num(project_num) {
    const True = await sample_detector_ground_info.findAll({
      attributes: { include: ['project_num'] },
      where: {
        project_num,

      }
    })

    return True
  }//查找土壤实时监测数据表中是否含有相应的地块信息



  /**
   *查询土壤背景数据表中是否含有相应监测点的样本信息
   *
   * @static
   * @param {*} sample_num
   * @param {*} assess_type
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findLabGroundProject_num(project_num) {
    const True = await sample_lab_ground_InfoSchema.findAll({
      attributes: { include: ['project_num'] },
      where: {
        project_num,

      }
    })

    return True
  }//查找土壤背景数据表中是否含有相应的地块信息

 /**
   *查询水实时监测表中是否含有相应监测点的样本信息
   *
   * @static
   * @param {*} sample_num
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findDetectorWaterProject_num(project_num) {
    const True = await sample_detector_water_info.findAll({
      attributes: { include: ['project_num'] },
      where: {
        project_num,

      }
    })

    return True
  }//查找水实时监测数据表中是否含有相应的地块信息



  /**
   *查询水背景数据表中是否含有相应监测点的样本信息
   *
   * @static
   * @param {*} sample_num
   * @param {*} assess_type
   * @returns
   * @memberof ExcelDataUpload
   */
  static async findLabWaterProject_num(project_num) {
    const True = await sample_lab_water_InfoSchema.findAll({
      attributes: { include: ['project_num'] },
      where: {
        project_num,

      }
    })

    return True
  }//查找水背景数据表中是否含有相应的地块信息





  /**
   *更新R土壤误差表
   *
   * @static
   * @param {*} item
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateRtable(data) {
    var Count
    var Update = []
    var insert = []
    for (let item of data) {
      await R_table_ground_info.findCreateFind(
        {
          where: {
            assess_type: item.assess_type,
            project_num: item.project_num,

          },
          defaults: item
        }).spread((item, created) => {
          if (created == false) {
            R_table_ground_info.update(
              item, {
              where: {
                roject_num: item.project_num,
                assess_type: item.assess_type,

              }
            }).then(item => {
              if (item[0] == 1) {
                Update.push(item)
              }
            })
          } else {
            insert.push(item)

          }
        })
    }

    if (Update.length != 0 && insert.length != 0) {
      Count = Update.length + insert.length
    } else if (Update.length != 0 && insert.length == 0) {
      Count = Update.length
    } else if (Update.length == 0 && insert.length != 0) {
      Count = insert.length
    } else if (Update.length == 0 && insert.length == 0) {
      Count = 0
    }
    return Count
  }//更新R2土壤表


 /**
   *更新R水误差表
   *
   * @static
   * @param {*} item
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateRwatertable(data) {
    var Count
    var Update = []
    var insert = []
    for (let item of data) {
      await R_table_water_info.findCreateFind(
        {
          where: {
            assess_type: item.assess_type,
            project_num: item.project_num,

          },
          defaults: item
        }).spread((item, created) => {
          if (created == false) {
            R_table_water_info.update(
              item, {
              where: {
                roject_num: item.project_num,
                assess_type: item.assess_type,

              }
            }).then(item => {
              if (item[0] == 1) {
                Update.push(item)
              }
            })
          } else {
            insert.push(item)

          }
        })
    }

    if (Update.length != 0 && insert.length != 0) {
      Count = Update.length + insert.length
    } else if (Update.length != 0 && insert.length == 0) {
      Count = Update.length
    } else if (Update.length == 0 && insert.length != 0) {
      Count = insert.length
    } else if (Update.length == 0 && insert.length == 0) {
      Count = 0
    }
    return Count
  }//更新R2水表




  static async findDetectorGround(project_num, point_num) {
    console.log(222,project_num, point_num);
    const have_point_num = await point_infoSchema.findAll({
      attributes: { include: ['assess_type'] },
      where: {
        project_num,
        point_num,
      }

    })
console.log(5555,have_point_num);

    return have_point_num
  }


  /**
   *更新Reference修复调查标准表
   *
   * @static
   * @param {*} data
   * @returns
   * @memberof ExcelDataUpload
   */
  static async updateReferenceTable(item) {
    var Count
    var Update = []
    
      await reference_info.update(
        item
        , {
          where: {
            type: item.type,
            reference_num: item.reference_num
          }
        }).then(item => {
          if (item[0] == 1) {
            Update.push(item)
          }
        })
    

    if (Update.length != 0) {
      Count = Update.length
    } else if (Update.length == 0) {
      Count = 0
    }
    return Count
  }//更新R2 表



}

module.exports = ExcelDataUpload 